/*
 * ProfileDAO.java
 *
 * 
 * 
 */

package com.ecable.daoImpl;

import oracle.jdbc.driver.*;

import java.util.Vector;
import com.ecable.bean.ProfileTO;
import com.ecable.bean.VillageTO;
import com.ecable.dao.AbstractDataAccessObject;
import com.ecable.daoI.ProfileDAOI;
import com.ecable.util.CoreHash;
import com.ecable.util.DateWrapper;
import com.ecable.util.LoggerManager;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties;

/**
 * 
 * @author
 */
public class ProfileDAO extends AbstractDataAccessObject implements ProfileDAOI {

	public Connection con;
	public PreparedStatement pstmt;
	private boolean flag;

	/** Creates a new instance of ProfileDAO */
	public ProfileDAO() {
		con = getConnection();

	}

	// User Registration
	public boolean registration(ProfileTO regbean) {

		String loginid = regbean.getLoginID();
		System.out.println("logint id :" + loginid);
		String password = regbean.getPassword();
		System.out.println(password);
		String firstname = regbean.getFirstName();
		System.out.println(firstname);
		String lastname = regbean.getLastName();
		System.out.println(lastname);
		String logintype = regbean.getLogintype();
		System.out.println("logintype :" + logintype);
		String secretquest = regbean.getSquest();
		System.out.println(secretquest);
		if (secretquest == null) {
			secretquest = regbean.getOwnSecretQuestion();
			System.out.println(secretquest);
		}
		String secretans = regbean.getSecretAnswer();
		System.out.println(secretans);
		String bdate = DateWrapper.parseDate(regbean.getBirthDate());
		System.out.println(bdate);
		String hno = regbean.getHno();
		System.out.println(hno);

		String street = regbean.getStreet();
		System.out.println(street);
		String city = regbean.getCity();
		System.out.println(city);
		String district = regbean.getDistrict();
		System.out.println("dist at dao :" + district);
		String state = regbean.getState();
		System.out.println(state);
		String country = regbean.getCountry();
		System.out.println(country);
		String pin = regbean.getPin();
		System.out.println(pin);
		String phonetype = regbean.getPhoneType();
		System.out.println(phonetype);
		String addresstype = regbean.getAddressType();
		System.out.println(addresstype);
		String phone = regbean.getPhone();

		System.out.println(phone);
		String fax = regbean.getFax();
		System.out.println(fax);
		String email = regbean.getEmail();
		System.out.println(email);
		String photo = regbean.getPhoto();
		System.out.println(photo);

		try {
			System.out.println("photo=" + photo);
			File f = new File(photo);
			FileInputStream fis = new FileInputStream(f);
			System.out.println("fole=" + f.length());

			CallableStatement cstmt = con
					.prepareCall("{call insertprocedure(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

			cstmt.setBinaryStream(1, fis, (int) f.length());
			cstmt.setString(2, firstname);
			cstmt.setString(3, lastname);
			cstmt.setString(4, bdate);
			cstmt.setString(5, loginid);
			cstmt.setString(6, password);
			cstmt.setString(7, logintype);
			cstmt.setString(8, secretquest);
			cstmt.setString(9, secretans);
			cstmt.setString(10, email);
			cstmt.setString(11, fax);
			cstmt.setString(12, addresstype);
			cstmt.setString(13, hno);
			cstmt.setString(14, street);
			cstmt.setString(15, city);
			cstmt.setString(16, district);
			cstmt.setString(17, state);
			cstmt.setString(18, country);
			cstmt.setString(19, pin);
			cstmt.setString(20, phone);
			cstmt.setString(21, phonetype);

			int i = cstmt.executeUpdate();
			if (i == 1) {
				flag = true;
			} else {
				flag = false;

			}
			con.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
			if (e
					.toString()
					.equalsIgnoreCase(
							"java.sql.SQLException: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12571: TNS:packet writer failure")) {
				flag = true;
				System.out.println("n===" + flag);
			}
			System.out.println(e);

		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
		return flag;
	}

	// Getting profile
	public ProfileTO getProfile(String loginname, String path) {
		ProfileTO rb = new ProfileTO();
		try {

			CallableStatement cs = con
					.prepareCall("{call showprofile(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

			/*
			 * 1 logid userdetails.loginid%type, 2 pass out
			 * userdetails.PASSWORD%type, 3 fname OUT
			 * userdetails.FIRSTNAME%type, 4 lname OUT
			 * userdetails.LASTNAME%type, 5 db OUT varchar2,
			 * 
			 * 6 email OUT userdetails.EMAILID%type, 7 fax OUT
			 * userdetails.FAXNO%type, 8 addresshome OUT
			 * addresses.ADDRESSTYPE%type, 9 housenohome OUT
			 * addresses.HOUSENO%type, 10 streethome OUT addresses.STREET%type,
			 * 11 cityhome OUT addresses.CITY%type, 12 statehome OUT
			 * addresses.STATE%type, 13 countryhome OUT addresses.COUNTRY%type,
			 * 14 pincodehome OUT addresses.PINCODE%type, 15 addressoffice OUT
			 * addresses.ADDRESSTYPE%type, 16 housenooffice OUT
			 * addresses.HOUSENO%type, 17 streetoffice OUT
			 * addresses.STREET%type, 18 cityoffice OUT addresses.CITY%type, 19
			 * stateoffice OUT addresses.STATE%type, 20 countryoffice OUT
			 * addresses.COUNTRY%type, 21 pincodeoffice OUT
			 * addresses.PINCODE%type, 22 addresspersonal OUT
			 * addresses.ADDRESSTYPE%type, 23 housenopersonal OUT
			 * addresses.HOUSENO%type, 24 streetpersonal OUT
			 * addresses.STREET%type, 25 citypersonal OUT addresses.CITY%type,
			 * 26 statepersonal OUT addresses.STATE%type, 27 countrypersonal OUT
			 * addresses.COUNTRY%type, 28 pincodepersonal OUT
			 * addresses.PINCODE%type, 29 phonehome OUT phones.PHONETYPE%type,
			 * 30 phonenohome OUT phones.PHONENO%type, 31 phoneoffice OUT
			 * phones.PHONETYPE%type, 32 phonenooffice OUT phones.PHONENO%type,
			 * 33 phonepersonal OUT phones.PHONETYPE%type, 34 phonenopersonal
			 * OUT phones.PHONENO%type, 35 photo OUT userdetails.PHOTOGRAPH%type
			 */

			cs.setString(1, loginname);
			cs.registerOutParameter(2, Types.VARCHAR);
			cs.registerOutParameter(3, Types.VARCHAR);
			cs.registerOutParameter(4, Types.VARCHAR);
			cs.registerOutParameter(5, Types.VARCHAR);
			cs.registerOutParameter(6, Types.VARCHAR);
			cs.registerOutParameter(7, Types.VARCHAR);
			cs.registerOutParameter(8, Types.VARCHAR);
			cs.registerOutParameter(9, Types.VARCHAR);
			cs.registerOutParameter(10, Types.VARCHAR);
			cs.registerOutParameter(11, Types.VARCHAR);
			cs.registerOutParameter(12, Types.VARCHAR);
			cs.registerOutParameter(13, Types.VARCHAR);
			cs.registerOutParameter(14, Types.VARCHAR);
			cs.registerOutParameter(15, Types.VARCHAR);
			cs.registerOutParameter(16, Types.VARCHAR);
			cs.registerOutParameter(17, Types.VARCHAR);
			cs.registerOutParameter(18, Types.VARCHAR);
			cs.registerOutParameter(19, Types.VARCHAR);
			cs.registerOutParameter(20, Types.VARCHAR);
			cs.registerOutParameter(21, Types.VARCHAR);
			cs.registerOutParameter(22, Types.VARCHAR);
			cs.registerOutParameter(23, Types.VARCHAR);
			cs.registerOutParameter(24, Types.VARCHAR);
			cs.registerOutParameter(25, Types.VARCHAR);
			cs.registerOutParameter(26, Types.VARCHAR);
			cs.registerOutParameter(27, Types.VARCHAR);
			cs.registerOutParameter(28, Types.VARCHAR);
			cs.registerOutParameter(29, Types.VARCHAR);
			cs.registerOutParameter(30, Types.VARCHAR);
			cs.registerOutParameter(31, Types.VARCHAR);
			cs.registerOutParameter(32, Types.VARCHAR);
			cs.registerOutParameter(33, Types.VARCHAR);
			cs.registerOutParameter(34, Types.VARCHAR);
			cs.registerOutParameter(35, Types.BLOB);
			cs.execute();
			rb.setPassword(cs.getString(2));
			rb.setFirstname(cs.getString(3));
			rb.setLastname(cs.getString(4));

			rb.setBdate(cs.getString(5));
			// rb.setPhoto(cs.getString());
			rb.setEmail(cs.getString(6));
			rb.setFax(cs.getString(7));
			rb.setHome(cs.getString(8));
			rb.setHno(cs.getString(9));
			rb.setStreet(cs.getString(10));
			rb.setCity(cs.getString(11));
			rb.setState(cs.getString(12));
			// rb.setPin(cs.getString(13));
			rb.setCountry(cs.getString(13));
			rb.setPin(cs.getString(14));
			rb.setOffice(cs.getString(15));

			/*
			 * 20 countryoffice OUT addresses.COUNTRY%type, 21 pincodeoffice OUT
			 * addresses.PINCODE%type, 22 addresspersonal OUT
			 * addresses.ADDRESSTYPE%type, 23 housenopersonal OUT
			 * addresses.HOUSENO%type, 24 streetpersonal OUT
			 * addresses.STREET%type, 25 citypersonal OUT addresses.CITY%type,
			 * 26 statepersonal OUT addresses.STATE%type, 27 countrypersonal OUT
			 * addresses.COUNTRY%type, 28 pincodepersonal OUT
			 * addresses.PINCODE%type,
			 * 
			 * 
			 */

			rb.setContact(cs.getString(22));

			rb.setPhone(cs.getString(30));

			String o = cs.getString(32);

			String s = cs.getString(34);

			Blob b = cs.getBlob(35);
			byte b1[] = b.getBytes(1, (int) b.length());
			OutputStream fout = new FileOutputStream(path + "/" + loginname
					+ "+.jpg");
			fout.write(b1);

		}

		catch (Exception e) {
			e.printStackTrace();
			LoggerManager.writeLogSevere(e);
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				LoggerManager.writeLogSevere(e);
			}

		}
		return rb;
	} // Modify ProfileTO

	public boolean modifyProfile(ProfileTO regbean) {
		String loginid = regbean.getLoginID();
		String firstname = regbean.getFirstName();
		String lastname = regbean.getLastName();
		// String bdate=DateWrapper.parseDate(regbean.getBirthDate());
		// home
		String hno = regbean.getHno();
		String home = regbean.getHome();
		String street = regbean.getStreet();
		String city = regbean.getCity();
		String state = regbean.getState();
		String country = regbean.getCountry();
		String pin = regbean.getPin();

		String phone = regbean.getPhone();
		// office

		String office = regbean.getOffice();

		String contact = regbean.getContact();

		String fax = regbean.getFax();
		String email = regbean.getEmail();
		String photo = regbean.getPhoto();
		String newdate = DateWrapper.parseDate(new Date());
		try {
			System.out.println("photo=" + photo);
			File f = new File(photo);
			FileInputStream fis = new FileInputStream(f);
			System.out.println("fole=" + f.length());

			// con.setAutoCommit(false);
			CallableStatement cs = con
					.prepareCall("{call changeprofile(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

			/*
			 * 
			 * 1 fname userdetails.FIRSTNAME%type, 2 lname
			 * userdetails.LASTNAME%type, 3 logid userdetails.LOGINID%type, 4
			 * photo userdetails.photograph%type, 5 email
			 * userdetails.EMAILID%type, 6 fax userdetails.FAXNO%type,
			 * 
			 */
			cs.setString(1, firstname);
			cs.setString(2, lastname);
			cs.setString(3, loginid);
			cs.setBinaryStream(4, fis, (int) f.length());
			// cs.setBinaryStream(4, fis,999);
			cs.setString(5, email);
			cs.setString(6, fax);

			/*
			 * 7 addresshome addresses.ADDRESSTYPE%type, 8 housenohome
			 * addresses.HOUSENO%type, 9 streethome addresses.STREET%type, 10
			 * cityhome addresses.CITY%type, 11 statehome addresses.STATE%type,
			 * 12 countryhome addresses.COUNTRY%type, 13 pincodehome
			 * addresses.PINCODE%type,
			 * 
			 */

			cs.setString(7, home);
			cs.setString(8, hno);
			cs.setString(9, street);
			cs.setString(10, city);
			cs.setString(11, state);
			cs.setString(12, country);
			cs.setString(13, pin);

			/*
			 * 14 addressoffice addresses.ADDRESSTYPE%type, 15 housenooffice
			 * addresses.HOUSENO%type, 16 streetoffice addresses.STREET%type, 17
			 * cityoffice addresses.CITY%type, 18 stateoffice
			 * addresses.STATE%type, 19 countryoffice addresses.COUNTRY%type, 20
			 * pincodeoffice addresses.PINCODE%type,
			 * 
			 */
			cs.setString(14, office);

			cs.setString(21, contact);

			cs.setString(29, phone);

			cs.registerOutParameter(34, Types.INTEGER);
			cs.execute();
			int n = cs.getInt(34);
			if (n > 0) {
				flag = true;
			}

			else {
				flag = false;
				con.rollback();
			}
			con.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException sex) {
				sex.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
		return flag;
	}

	// getreport fromdate todate
	public CoreHash getReportFromTo(String sdate, String edate) {

		Properties p = getProperties();
		String pattern = "";
		if (p.getProperty("dbname").equals("access"))
			pattern = "#";
		CoreHash aCoreHash = new CoreHash();
		aCoreHash.clear();
		System.out.println(" aCoreHash--" + aCoreHash.isEmpty());
		int sno = 1;
		Statement st;
		ProfileTO aProfile = null;
		try {

			st = con.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT DISTINCT(UD.LOGINID ),UD.FIRSTNAME,UD.LASTNAME,UD.DOB,UD.DOR,UD.EMAILID FROM USERDETAILS UD,LOGINMASTER LM WHERE UD.USERID=LM.USERID AND TO_CHAR(LOGINDATE,'DD-MM-YYYY')>='"
							+ sdate
							+ "' AND TO_CHAR(LOGINDATE,'DD-MM-YYYY')<='"
							+ edate + "'");
			while (rs.next()) {
				aProfile = new ProfileTO();

				aProfile.setLoginID(rs.getString(1));
				aProfile.setFirstname(rs.getString(2));
				aProfile.setLastName(rs.getString(3));
				String date = DateWrapper.parseDate(rs.getDate(4)).trim();
				aProfile.setBirthDate(date);
				aProfile
						.setRegDate(DateWrapper.parseDate(rs.getDate(5)).trim());
				aProfile.setEmail(rs.getString(6));

				aCoreHash.put(new Integer(sno), aProfile);
				sno++;

			}
		} catch (Exception e) {
			e.printStackTrace();
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				if (con != null)
					con.close();

			} catch (Exception e) {
			}
		}
		return aCoreHash;
	}

	// presentloginuser getting

	public CoreHash getReportPresent() {

		CoreHash aCoreHash = new CoreHash();
		aCoreHash.clear();
		System.out.println("aCoreHash--" + aCoreHash.isEmpty());
		int sno = 1;
		CallableStatement cstmt;
		ProfileTO aProfile = null;
		try {

			cstmt = con
					.prepareCall("{call REF_CURSOR_TEST.GET_ACCOUNTS_PROCEDURE(?)}");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.executeUpdate();
			ResultSet rs = ((OracleCallableStatement) cstmt).getCursor(1);
			while (rs.next()) {
				aProfile = new ProfileTO();

				aProfile.setLoginID(rs.getString(1));
				aProfile.setFirstname(rs.getString(2));
				aProfile.setLastName(rs.getString(3));
				String date = DateWrapper.parseDate(rs.getDate(4)).trim();
				aProfile.setBirthDate(date);
				aProfile
						.setRegDate(DateWrapper.parseDate(rs.getDate(5)).trim());
				aProfile.setEmail(rs.getString(6));

				aCoreHash.put(new Integer(sno), aProfile);
				sno++;

			}
		} catch (Exception e) {
			e.printStackTrace();
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				if (con != null)
					con.close();

			} catch (Exception e) {
			}
		}
		return aCoreHash;
	}

	public boolean changeAccountStatus(String loginid, int status) {
		try {

			con.setAutoCommit(false);
			PreparedStatement cstmt = con
					.prepareStatement("UPDATE logindetails SET loginstatus=? WHERE loginid=?");

			cstmt.setInt(1, status);
			cstmt.setString(2, loginid);
			int i = cstmt.executeUpdate();

			if (i == 1) {
				flag = true;
				con.commit();
			} else {
				flag = false;
				con.rollback();
			}
			con.close();
		} catch (SQLException ex) {
			LoggerManager.writeLogSevere(ex);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				LoggerManager.writeLogSevere(se);
			}
		} catch (Exception e) {
			LoggerManager.writeLogSevere(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				LoggerManager.writeLogSevere(se);
			}
		}
		return flag;
	}

	public Vector<ProfileTO> getViewProfile(String realpath, String user,
			String sname, String dname, String vname) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();
		ProfileTO rb = null;
		v.clear();

		try {

			System.out.println("in DAOImpl path is..............." + realpath);
			pstmt = con
					.prepareStatement("select u.USERID,u.firstname,u.DOR,u.emailid,u.faxno,"
							+ "p.phoneno,a.state,a.district,a.city,u.photograph from userdetails u,"
							+ "phones p,addresses a where logintype=? and u.userid =p.userid "
							+ "and u.userid=a.userid and a.state=? and a.district =?  "
							+ "and a.city=? and u.status='Active'");
			pstmt.setString(1, user);
			pstmt.setString(2, sname);
			pstmt.setString(3, dname);
			pstmt.setString(4, vname);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String path = realpath;
				rb = new ProfileTO();
				rb.setId(rs.getString(1));
				rb.setFirstname(rs.getString(2));
				rb.setDOR(DateWrapper.parseDate(rs.getDate(3)));
				rb.setEmail(rs.getString(4));
				rb.setFax(rs.getString(5));
				rb.setPhone(rs.getString(6));
				rb.setState(rs.getString(7));
				rb.setDistrict(rs.getString(8));
				rb.setCity(rs.getString(9));
				System.out.println("testing " + rs.getString(9));
				Blob b = rs.getBlob(10);
				byte b1[] = b.getBytes(1, (int) b.length());// new
				// byte[(int)b.length()];
				// InputStream in=photo.getBinaryStream();
				path = path + "/" + rs.getString(1) + ".jpg";
				System.out.println("path :" + path);
				OutputStream fout = new FileOutputStream(path);
				fout.write(b1);

				rb.setPhoto(path);

				v.add(rb);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public Vector<ProfileTO> getViewProfile(int userid) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();
		ProfileTO rb = null;
		v.clear();

		try {

			System.out.println("in DAOImpl path is..............." + userid);
			pstmt = con
					.prepareStatement("select u.userid,u.firstname,u.emailid,u.faxno,p.phoneno from userdetails u,phones p where u.userid=? and u.userid =p.userid");

			pstmt.setInt(1, userid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				rb = new ProfileTO();
				rb.setUserid(rs.getInt(1));
				System.out.println("testing :" + rs.getInt(1));
				rb.setFirstname(rs.getString(2));
				rb.setEmail(rs.getString(3));
				rb.setFax(rs.getString(4));
				rb.setPhone(rs.getString(5));
				v.add(rb);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean updateOperator(ProfileTO opf) {

		boolean flag = true;
		int userid = opf.getUserid();
		System.out.println(userid);
		String firstname = opf.getFirstname();
		System.out.println(firstname);
		String email = opf.getEmail();
		String faxno = opf.getFax();
		String phone = opf.getPhone();
		System.out.println(phone);
		try {

			CallableStatement cs = con
					.prepareCall("{call updateoperator(?,?,?,?,?,?)}");
			cs.setInt(1, userid);
			cs.setString(2, firstname);
			cs.setString(3, email);
			cs.setString(4, faxno);
			cs.setString(5, phone);
			cs.registerOutParameter(6, Types.INTEGER);
			cs.execute();
			int b = cs.getInt(6);
			System.out.println(b);
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			System.out.println("firstt" + e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("firstttttttt" + e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println("miffleeeeeeeeeeeeeee" + se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println("lasttttttttt" + e);
			}
		}
		return flag;
	}

	public Vector<ProfileTO> viewUser(String path, String user) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();

		String userid = "";

		ProfileTO rb = null;
		v.clear();

		try {

			pstmt = con
					.prepareStatement("select u.userid,u.firstname,u.emailid,u.faxno,u.photograph,p.phoneno from userdetails u,phones p where u.firstname=? and u.userid =p.userid");

			pstmt.setString(1, user);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				rb = new ProfileTO();
				rb.setUserid(rs.getInt(1));
				rb.setFirstname(rs.getString(2));
				System.out.println("haiiiiiii" + rs.getString(2));
				rb.setEmail(rs.getString(3));
				rb.setFax(rs.getString(4));
				rb.setPhone(rs.getString(6));
				Blob b = rs.getBlob(5);
				byte b1[] = b.getBytes(1, (int) b.length());// new
				// byte[(int)b.length()];
				// InputStream in=photo.getBinaryStream();
				OutputStream fout = new FileOutputStream(path + "/" + user
						+ ".jpg");
				fout.write(b1);

				rb.setPhoto(user + ".jpg");

				v.add(rb);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean updateUser(ProfileTO uUser) {

		boolean flag = true;
		int userid = uUser.getUserid();
		System.out.println(userid);
		String firstname = uUser.getFirstname();
		System.out.println(firstname);
		String email = uUser.getEmail();
		String faxno = uUser.getFax();
		String phone = uUser.getPhone();
		System.out.println(phone);
		try {

			CallableStatement cs = con
					.prepareCall("{call updateoperator(?,?,?,?,?,?)}");
			cs.setInt(1, userid);
			cs.setString(2, firstname);
			cs.setString(3, email);
			cs.setString(4, faxno);
			cs.setString(5, phone);
			cs.registerOutParameter(6, Types.INTEGER);
			cs.execute();
			int b = cs.getInt(6);
			System.out.println(b);
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();

			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();

			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public Vector<ProfileTO> getViewRequestedOperater(String usertype) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();
		String userid = "";
		ProfileTO rb = null;
		v.clear();
		try {

			pstmt = con
					.prepareStatement("select u.USERID,u.firstname,u.DOR,u.emailid,u.faxno,p.phoneno,a.state,a.district,a.city,u.status from userdetails u,phones p,addresses a where logintype=? and u.userid =p.userid and u.userid=a.userid and u.status='Request'");

			pstmt.setString(1, usertype);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				rb = new ProfileTO();
				rb.setUserid(rs.getInt(1));
				rb.setFirstname(rs.getString(2));
				System.out.println("haiiiiiii" + rs.getString(2));
				rb.setDate(DateWrapper.parseDate(rs.getDate(3)));
				rb.setEmail(rs.getString(4));
				rb.setFax(rs.getString(5));
				rb.setPhone(rs.getString(6));
				rb.setState(rs.getString(7));
				rb.setDistrict(rs.getString(8));
				rb.setCity(rs.getString(9));
				rb.setStatus(rs.getString(10));
				v.add(rb);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public Vector<ProfileTO> getViewRequestedCustomer(ProfileTO rb) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();
		String userid = "";

		v.clear();
		try {

			pstmt = con
					.prepareStatement("select u.USERID,u.firstname,u.DOR,u.emailid,u.faxno,"
							+ "p.phoneno,a.state,a.district,a.city,u.status from userdetails u,phones p,addresses a where u.logintype=?"
							+ " and u.userid =p.userid and u.userid=a.userid and  a.state=? and"
							+ " a.district=? and a.city=? and u.status='Request'");

			pstmt.setString(1, rb.getLogintype());
			pstmt.setString(2, rb.getState());
			pstmt.setString(3, rb.getDistrict());
			pstmt.setString(4, rb.getCity());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				rb = new ProfileTO();
				rb.setUserid(rs.getInt(1));
				rb.setFirstname(rs.getString(2));
				System.out.println("haiiiiiii" + rs.getString(2));
				rb.setDate(DateWrapper.parseDate(rs.getDate(3)));
				rb.setEmail(rs.getString(4));
				rb.setFax(rs.getString(5));
				rb.setPhone(rs.getString(6));
				rb.setState(rs.getString(7));
				rb.setDistrict(rs.getString(8));
				rb.setCity(rs.getString(9));
				rb.setStatus(rs.getString(10));
				v.add(rb);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean updateOperatorStatus(int userid) {
		try {

			System.out.println("user id at DAO:" + userid);
			pstmt = con
					.prepareStatement("update userdetails set status='Active' where userid=?");
			pstmt.setInt(1, userid);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			}
		} catch (SQLException se) {
			System.out.println(se);
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			System.out.println(e);
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return flag;
	}
	public boolean updateCustomerStatus(int userid) {
		try {

			System.out.println("user id at DAO:" + userid);
			pstmt = con
					.prepareStatement("update userdetails set status='Request' where userid=?");
			pstmt.setInt(1, userid);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			}
		} catch (SQLException se) {
			System.out.println(se);
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			System.out.println(e);
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return flag;
	}


	public boolean deleteOperator(int userid) {
		try {
			pstmt = con.prepareStatement("delete userdetails where userid=?");
			pstmt.setInt(1, userid);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return flag;
	}
}
